package server.dao;

import common.bodies.FriendShipBody;
import common.bodies.NotificationBody;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FriendDao {
    public List<FriendShipBody> getFriendList (Integer userId){

           String sql = "select * from chat_room.friendships where (user1_id = ? or user2_id =?) and status = 1";
            List<FriendShipBody> friends = null;;
            try (Connection conn = SQLConnection.getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
               pstmt.setInt(1,userId);
                pstmt.setInt(2,userId);
                ResultSet rs = pstmt.executeQuery();
                friends = new ArrayList<>();
                while (rs.next()){
                    Integer user1Id = rs.getInt("user1_id");
                    Integer user2Id = rs.getInt("user2_id");
                    String user1Name = rs.getString("user1_name");
                    String user2Name = rs.getString("user2_name");
                    Integer status = rs.getInt("status");
                    friends.add(new FriendShipBody(user1Id,user1Name,user2Id,user2Name,status));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            return friends ;
    }
    public List<FriendShipBody> searchNewFriend(String userName, Integer userId, String keyword) {
        List<FriendShipBody> results = new ArrayList<>();

        String sql = """
        SELECT u.id, u.name,
               f.user1_id, f.user2_id, f.status
        FROM users u
        LEFT JOIN friendships f 
          ON (f.user1_id = ? AND f.user2_id = u.id)
           OR (f.user2_id = ? AND f.user1_id = u.id)
        WHERE u.name LIKE ? AND u.id != ?
    """;

        try (Connection conn = SQLConnection.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {

            stmt.setInt(1, userId); // f.user1_id = ?
            stmt.setInt(2, userId); // f.user2_id = ?
            stmt.setString(3, "%" + keyword + "%"); // u.name LIKE ?
            stmt.setInt(4, userId); // 排除自己

            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    int targetId = rs.getInt("id");
                    String targetName = rs.getString("name");

                    Integer user1Id = (Integer) rs.getObject("user1_id");
                    Integer user2Id = (Integer) rs.getObject("user2_id");
                    Integer status = (Integer) rs.getObject("status");

                    FriendShipBody body = new FriendShipBody();

                    if (user1Id != null && user2Id != null && status != null) {
                        // 已存在好友关系，按数据库原样封装
                        body.setUser1Id(user1Id);
                        body.setUser2Id(user2Id);
                        body.setStatus(status);

                        // 名字补充
                        if (userId.equals(user1Id)) {
                            body.setUser1Name(userName);
                            body.setUser2Name(targetName);
                        } else {
                            body.setUser2Name(userName);
                            body.setUser1Name(targetName);
                        }
                    } else {
                        body.setUser1Id(userId);
                        body.setUser1Name(userName);
                        body.setUser2Id(targetId);
                        body.setUser2Name(targetName);
                        body.setStatus(-1);
                    }

                    results.add(body);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return results;
    }


    public Boolean updateFriendShip (FriendShipBody ship) {
        String selectSql = "SELECT user1_id, user2_id FROM chat_room.friendships WHERE " +
                "(user1_id = ? AND user2_id = ?) OR (user1_id = ? AND user2_id = ?)";

        String insertSql = "INSERT INTO chat_room.friendships (user1_id, user1_name, user2_id, user2_name, status) " +
                "VALUES (?, ?, ?, ?, ?)";

        String deleteSql = "DELETE FROM chat_room.friendships WHERE " +
                "(user1_id = ? AND user2_id = ?) OR (user1_id = ? AND user2_id = ?)";

        try (Connection conn = SQLConnection.getConnection();
             PreparedStatement checkStmt = conn.prepareStatement(selectSql)) {

            checkStmt.setInt(1, ship.getUser1Id());
            checkStmt.setInt(2, ship.getUser2Id());
            checkStmt.setInt(3, ship.getUser2Id());
            checkStmt.setInt(4, ship.getUser1Id());

            ResultSet rs = checkStmt.executeQuery();

            if (rs.next()) {
                int dbUser1 = rs.getInt("user1_id");
                int dbUser2 = rs.getInt("user2_id");

                // 如果顺序不同，则删除旧记录
                if (dbUser1 != ship.getUser1Id() || dbUser2 != ship.getUser2Id()) {
                    try (PreparedStatement deleteStmt = conn.prepareStatement(deleteSql)) {
                        deleteStmt.setInt(1, ship.getUser1Id());
                        deleteStmt.setInt(2, ship.getUser2Id());
                        deleteStmt.setInt(3, ship.getUser2Id());
                        deleteStmt.setInt(4, ship.getUser1Id());
                        deleteStmt.executeUpdate();
                    }
                } else {
                    // 顺序一样可以跳过删除
                    try (PreparedStatement deleteStmt = conn.prepareStatement(deleteSql)) {
                        deleteStmt.setInt(1, ship.getUser1Id());
                        deleteStmt.setInt(2, ship.getUser2Id());
                        deleteStmt.setInt(3, ship.getUser2Id());
                        deleteStmt.setInt(4, ship.getUser1Id());
                        deleteStmt.executeUpdate(); // 可选，保证只有一条数据存在
                    }
                }
            }

            // 插入新的记录
            try (PreparedStatement insertStmt = conn.prepareStatement(insertSql)) {
                insertStmt.setInt(1, ship.getUser1Id());
                insertStmt.setString(2, ship.getUser1Name());
                insertStmt.setInt(3, ship.getUser2Id());
                insertStmt.setString(4, ship.getUser2Name());
                insertStmt.setInt(5, ship.getStatus());
                return insertStmt.executeUpdate() > 0;
            }

        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public List<NotificationBody> friendRequestList(Integer userId) {
        List<NotificationBody> list = new ArrayList<>();

        String sql = "SELECT user1_id, user1_name, user2_id, user2_name, status " +
                "FROM chat_room.friendships " +
                "WHERE status = 0 AND user2_id = ?";

        try (Connection conn = SQLConnection.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, userId);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    NotificationBody notif = new NotificationBody(
                            "friend",
                            rs.getInt("user1_id"),
                            rs.getString("user1_name"),
                            rs.getInt("user2_id"),
                            rs.getString("user2_name"),
                            null,
                            null,
                            null,
                            rs.getInt("status")
                    );
                    list.add(notif);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }

    public Boolean upDateFriend (Integer user1Id,Integer user2Id,Integer status){
        String sql = "update chat_room.friendships set status = ? where user1_id = ? and user2_id = ?";
        try (Connection conn = SQLConnection.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1,status);
            pstmt.setInt(2,user1Id);
            pstmt.setInt(3,user2Id);
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

}
